﻿using DotNetExtensions.AspNet.Helpers;
using DotNetExtensions.AspNet.SQLites.Attributes;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace DotNetExtensions.AspNet.SQLites
{
    public class DbSet<TEntity> where TEntity : class
    {
        private readonly string connectionString;
        private readonly TableInfo tableInfo;
        public DbSet(string connectinoString)
        {
            this.connectionString = connectinoString;
            tableInfo = TableInfoFactory.Default.Get<TEntity>();
        }
        public SQLiteConnection CreateConnection()
        {
            var connectionInfo = SQLiteConnectionFactory.Default.Create(connectionString);
            if (connectionInfo == null) throw new Exception();
            if (connectionInfo.Connection.State == System.Data.ConnectionState.Closed) connectionInfo.Connection.Open();
            return connectionInfo.Connection;
        }

        public bool Insert(TEntity data)
        {
            string commandText = tableInfo.InsertCommandText;
            return ExecuteNonQuery(data, commandText) > 0;
        }
        public int InsertRange(List<TEntity> data)
        {
            string commandText = tableInfo.InsertCommandText;
            return ExecuteNonQuery(data, commandText);
        }
        public bool Update(TEntity data, bool updateAll = true)
        {
            string commandText = tableInfo.UpdateCommandText;
            if (updateAll == false) commandText = tableInfo.RefleshUpdateCommandText(data);
            return ExecuteNonQuery(data, commandText) > 0;
        }
        public int UpdateRange(List<TEntity> data, bool updateAll = true)
        {
            return ExecuteUpdateQuery(data, updateAll);
        }
        public int UpdateRangeWhere(List<TEntity> entities, Expression<Func<TEntity, bool>> expression)
        {
            var whereCommand = SQLiteCommandBuilder.Default.ParseUpdateWhereText(expression);

            int total = 0;
            using (var connection = CreateConnection())
            {
                var transaction = connection.BeginTransaction();
                try
                {
                    foreach (var item in entities)
                    {
                        string commandText = $"{tableInfo.UpdateCommandPlainText} {whereCommand.Text}";
                        using (var command = connection.CreateCommand())
                        {
                            command.Transaction = transaction;
                            SQLiteCommandBuilder.Default.BindCommandValue(item, command, commandText, whereCommand.Parameters);
                            total += command.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback(); throw;
                }
            }
            return total;
        }
        public bool UpdateWhere(TEntity data, Expression<Func<TEntity, bool>> expression)
        {
            var whereCommand = SQLiteCommandBuilder.Default.ParseUpdateWhereText(expression);

            int total = 0;
            using (var connection = CreateConnection())
            {
                try
                {
                    string commandText = $"{tableInfo.UpdateCommandPlainText} {whereCommand.Text}";
                    using (var command = connection.CreateCommand())
                    {
                        SQLiteCommandBuilder.Default.BindCommandValue(data, command, commandText, whereCommand.Parameters);
                        total += command.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            return total > 0;
        }
        public bool Update<TWhere>(TEntity data, Func<TEntity, TWhere> whereSelector)
        {
            TWhere where = whereSelector.Invoke(data);
            Type whereType = where.GetType();
            if (TypeHelper.IsBasicType(whereType)) return false;

            string whereText = "WHERE 1=0";
            List<string> whereKeys = new List<string>();
           
            foreach (var item in whereType.GetProperties())
            {
                whereKeys.Add($"{tableInfo.Prefix}{item.Name}");
            }

            if (whereKeys.Any())
            {
                whereText = string.Join(" AND ", whereKeys);
            }

            List<string> test = new List<string>();
            test.GroupBy(c => new { });
            int total = 0;
            using (var connection = CreateConnection())
            {
                try
                {
                    string commandText = $"{tableInfo.UpdateCommandPlainText} WHERE {whereText}";
                    using (var command = connection.CreateCommand())
                    {
                        SQLiteCommandBuilder.Default.BindCommandValue(data, command, commandText);
                        total += command.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            return total > 0;
        }

        public bool UpdateBy<TProperty>(TEntity data, Expression<Func<TEntity, TProperty>> whereSelector)
        {
            int total = 0;
            using (var connection = CreateConnection())
            {
                try
                {
                    string commandText = SQLiteCommandBuilder.Default.ParseUpdateWhereText(whereSelector);
                    using (var command = connection.CreateCommand())
                    {
                        SQLiteCommandBuilder.Default.BindCommandValue(data, command, commandText);
                        total += command.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            return total > 0;
        }

        public bool Delete(TEntity data)
        {
            string commandText = tableInfo.DeleteCommandText;
            return ExecuteNonQuery(data, commandText) > 0;
        }

        public int Delete(List<TEntity> data)
        {
            string commandText = tableInfo.DeleteCommandText;
            return ExecuteNonQuery(data, commandText);
        }

        public int Delete(Expression<Func<TEntity, bool>> expression = null)
        {
            var command = SQLiteCommandBuilder.Default.ParseDeleteCommandText(expression);
            return ExecuteNonQuery(command.Text, command.Parameters);
        }

        private int ExecuteNonQuery(TEntity data, string commandText)
        {
            int count = 0;
            using (var connection = CreateConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    SQLiteCommandBuilder.Default.BindCommandValue(data, command, commandText);
                    count = command.ExecuteNonQuery();
                }
            }
            return count;
        }

        private int ExecuteUpdateQuery(List<TEntity> entities, bool updateAll = true)
        {

            int total = 0;
            using (var connection = CreateConnection())
            {
                var transaction = connection.BeginTransaction();
                try
                {
                    foreach (var item in entities)
                    {
                        string commandText = tableInfo.UpdateCommandText;
                        if (updateAll == false) commandText = tableInfo.RefleshUpdateCommandText(item);
                        using (var command = connection.CreateCommand())
                        {
                            command.Transaction = transaction;
                            SQLiteCommandBuilder.Default.BindCommandValue(item, command, commandText);
                            total += command.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback(); throw;
                }
            }
            return total;
        }

        private int ExecuteNonQuery(List<TEntity> entities, string commandText)
        {
            int total = 0;
            using (var connection = CreateConnection())
            {
                var transaction = connection.BeginTransaction();
                try
                {
                    foreach (var item in entities)
                    {
                        using (var command = connection.CreateCommand())
                        {
                            command.Transaction = transaction;
                            SQLiteCommandBuilder.Default.BindCommandValue(item, command, commandText);
                            total += command.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback(); throw;
                }
            }
            return total;

        }

        public int ExecuteNonQuery(string commandText, Dictionary<string, object> parameters)
        {
            int count = 0;
            using (var connection = CreateConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    foreach (var item in parameters)
                    {
                        command.Parameters.AddWithValue(item.Key, item.Value);
                    }

                    count = command.ExecuteNonQuery();
                }
            }
            return count;

        }
        public TResult ExecuteScalar<TResult>(string commandText, Dictionary<string, object> parameters)
        {
            using (var connection = CreateConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    foreach (var item in parameters)
                    {
                        command.Parameters.AddWithValue(item.Key, item.Value);
                    }

                    object value = command.ExecuteScalar();

                    if (value == null || value == DBNull.Value) return default;

                    return (TResult)Convert.ChangeType(value, typeof(TResult));
                }
            }
        }
        public IEnumerable<TEntity> DataReader(string commandText, Dictionary<string, object> parameters)
        {
            List<TEntity> rows = new List<TEntity>();
            using (var connection = CreateConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    foreach (var item in parameters)
                    {
                        command.Parameters.AddWithValue(item.Key, item.Value);
                    }
                    SQLiteDataReader sqliteDataReader = command.ExecuteReader();
                    var propertyes = typeof(TEntity).GetProperties();

                    while (sqliteDataReader.Read())
                    {
                        TEntity row = (TEntity)Activator.CreateInstance(typeof(TEntity));
                        foreach (var item in propertyes)
                        {
                            if (!item.CanWrite) continue;

                            object value = sqliteDataReader[item.Name];
                            var attribute = item.GetCustomAttribute<SQLiteColumnAttribute>();
                            if (value == DBNull.Value) continue;

                            if (item.PropertyType.IsEnum)
                            {
                                item.SetValue(row, Enum.ToObject(item.PropertyType, value), null);
                            }
                            else
                            {
                                if (attribute != null && attribute.IsJson && value != null)
                                {
                                    value = JsonConvert.DeserializeObject(value.ToString(), item.PropertyType);
                                }
                                else if (attribute != null && attribute.IsType && value != null)
                                {
                                    TypeValue typeValue = JsonConvert.DeserializeObject<TypeValue>(value.ToString());
                                    value = TypeHelper.TryParse(typeValue.Module, typeValue.Clazz);
                                }
                                else if (item.PropertyType == typeof(DateTime) || item.PropertyType == typeof(DateTime?))
                                {
                                    value = DateTime.Parse(value.ToString());
                                }
                                var nullableType = Nullable.GetUnderlyingType(item.PropertyType);
                                if (nullableType != null)
                                {
                                    value = Convert.ChangeType(value, nullableType);
                                }
                                else if (item.PropertyType != value.GetType())
                                {
                                    value = Convert.ChangeType(value, item.PropertyType);
                                }

                                item.SetValue(row, value, null);
                            }
                        }

                        rows.Add(row);
                    }
                }
            }

            return rows ?? new List<TEntity>();

        }

        public IEnumerable<TEntity> DataReader(Expression<Func<TEntity, bool>> expression = null, Expression<Func<TEntity, object>> orderBy = null)
        {
            var command = SQLiteCommandBuilder.Default.ParseQueryCommandText<TEntity, object>(expression, orderBy);
            return DataReader(command.Text, command.Parameters);
        }

        public IEnumerable<TEntity> Get(Expression<Func<TEntity, bool>> expression = null, Expression<Func<TEntity, object>> orderBy = null)
        {
            return DataReader(expression, orderBy);
        }

        public TEntity GetSingle(Expression<Func<TEntity, bool>> expression = null)
        {
            return Get(expression).FirstOrDefault();
        }

        public Pager<TEntity> Get(int offset, int count, Expression<Func<TEntity, bool>> expression = null, Expression<Func<TEntity, object>> orderBy = null)
        {
            Pager<TEntity> result = new Pager<TEntity>();
            var command = SQLiteCommandBuilder.Default.ParseQueryCommandText(expression, offset, count, orderBy);
            result.Data = DataReader(command.Text, command.Parameters);
            result.Total = ExecuteScalar<int>(command.CountText, command.Parameters);

            return result;
        }

        public int Count(Expression<Func<TEntity, bool>> expression = null)
        {
            var command = SQLiteCommandBuilder.Default.ParseCountCommandText(expression);

            return ExecuteScalar<int>(command.Text, command.Parameters);
        }

        public bool Exists(Expression<Func<TEntity, bool>> expression = null)
        {
            var command = SQLiteCommandBuilder.Default.ParseCountCommandText(expression);

            return ExecuteScalar<int>(command.Text, command.Parameters) > 0;
        }
    }
}
